﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeRepositoryConsole.ORM.ADO.NET
{
    public class DataReader
    {
        // Provides a way of reading a forward-only stream of rows from a SQL Server database. 

        /**
         *  SqlDataReader:
         *  Holds the connection open until you are finished (don't forget to close it!).
         *  Can typically only be iterated over once
         *  Is not as useful for updating back to the database
         *  On the other hand, it:
         *  Only has one record in memory at a time rather than an entire result set (this can be huge)
         *  Is about as fast as you can get for that one iteration
         *  Allows you start processing results sooner (once the first record is available)
         */


        public DataTable GetDatatable1()
        {
            SqlConnection cn = new SqlConnection("Server=.;Database=AdventureWorks2014;User Id=sa;Password=123456");
            cn.Open();
            string query = "select top 100 * from [Person].[Person]";
            SqlCommand cmd = new SqlCommand(query, cn);

            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            cn.Close();
            return dt;
        }

        /// <summary>
        /// If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in orde
        /// </summary>
        public void RetrieveResult()
        {
            SqlConnection cn = new SqlConnection("Server=.;Database=AdventureWorks2014;User Id=sa;Password=lh123456");
            cn.Open();
            SqlCommand cmd = new SqlCommand("select top 100 * from [Person].[Person]", cn);

            SqlDataReader reader= cmd.ExecuteReader();
            if (reader.HasRows)
            {
                var dt = reader.GetSchemaTable();
                while (reader.Read())
                {
                    var column1 = reader.GetString(1);
                    IDataRecord dataRecord = (IDataRecord)reader;
                    column1 = dataRecord.GetString(1);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }

            reader.Close();
            cn.Close();
        }

        public void RetrieveMultipleResult()
        {
            SqlConnection cn = new SqlConnection("Server=.;Database=AdventureWorks2014;User Id=sa;Password=lh123456");
            cn.Open();
            SqlCommand cmd = new SqlCommand("select top 100 * from [Person].[Person];select top 100 * from[Person].[PersonPhone];", cn);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.HasRows)
            {
                var name1 = reader.GetName(1);

                while (reader.Read())
                {
                    object column1 = reader.GetValue(0);
                }

                reader.NextResult();
            }

            reader.Close();
            cn.Close();

        }

    }
}
